/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Data;

import config.ServerConfig;
import Domain.OracleWorkFlow;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author JoanIsaac
 */
public class  DBAccess {
    
    Connection con;

    public Connection getCon() {
        return con;
    }
    
    java.sql.PreparedStatement insertCustomer;
    java.sql.PreparedStatement insertlineitem;
    java.sql.PreparedStatement insertNation;
    java.sql.PreparedStatement orders;
    java.sql.PreparedStatement part;
    java.sql.PreparedStatement partsupp;
    java.sql.PreparedStatement region;
    java.sql.PreparedStatement supplier;
    java.sql.PreparedStatement select1;
    java.sql.PreparedStatement select2;
    java.sql.PreparedStatement select3;
    java.sql.PreparedStatement select4;
   
    
    public DBAccess() {
      String user = ServerConfig.getInstance().DBUsername;
      String password = ServerConfig.getInstance().DBPassword;
      try
      {
          Class.forName("oracle.jdbc.driver.OracleDriver");
          con = null;
          Properties propRoot = new Properties();
          propRoot.setProperty("user", user);
          propRoot.setProperty("password", password);
          con=DriverManager.getConnection(ServerConfig.getInstance().DB_Adress, propRoot);
          con.setAutoCommit(false);
          if(con.isValid(10))
              inicializarPreparedStatements();
          else
              System.out.println("Conexion no valida");
      }
      catch(Exception e)
      {
        System.err.println("Exception: " + e.getMessage());
        System.out.println("Driver no encontrado");
      }
    }
    /**
     CREATE TABLE customer(
    C_CustKey int NULL,
    C_Name varchar2(64) NULL,
    C_Address varchar2(64) NULL,
    C_NationKey int NULL,
    C_Phone varchar2(64) NULL,
    C_AcctBal number(13, 2) NULL,
    C_MktSegment varchar2(64) NULL,
    C_Comment varchar2(120) NULL,
    skip varchar2(64) NULL
);

CREATE TABLE lineitem(
    L_OrderKey int NULL,
    L_PartKey int NULL,
    L_SuppKey int NULL,
    L_LineNumber int NULL,
    L_Quantity int NULL,
    L_ExtendedPrice number(13, 2) NULL,
    L_Discount number(13, 2) NULL,
    L_Tax number(13, 2) NULL,
    L_ReturnFlag varchar2(64) NULL,
    L_LineStatus varchar2(64) NULL,
    L_ShipDate date NULL,
    L_CommitDate date NULL,
    L_ReceiptDate date NULL,
    L_ShipInstruct varchar2(64) NULL,
    L_ShipMode varchar2(64) NULL,
    L_Comment varchar2(64) NULL,
    skip varchar2(64) NULL
);

CREATE TABLE nation(
    N_NationKey int NULL,
    N_Name varchar2(64) NULL,
    N_RegionKey int NULL,
    N_Comment varchar2(160) NULL,
    skip varchar2(64) NULL
);

CREATE TABLE orders(
    O_OrderKey int NULL,
    O_CustKey int NULL,
    O_OrderStatus varchar2(64) NULL,
    O_TotalPrice number(13, 2) NULL,
    O_OrderDate date NULL,
    O_OrderPriority varchar2(15) NULL,
    O_Clerk varchar2(64) NULL,
    O_ShipPriority int NULL,
    O_Comment varchar2(80) NULL,
    skip varchar2(64) NULL
);

CREATE TABLE part(
    P_PartKey int NULL,
    P_Name varchar2(64) NULL,
    P_Mfgr varchar2(64) NULL,
    P_Brand varchar2(64) NULL,
    P_Type varchar2(64) NULL,
    P_Size int NULL,
    P_Container varchar2(64) NULL,
    P_RetailPrice number(13, 2) NULL,
    P_Comment varchar2(64) NULL,
    skip varchar2(64) NULL
);

CREATE TABLE partsupp(
    PS_PartKey int NULL,
    PS_SuppKey int NULL,
    PS_AvailQty int NULL,
    PS_SupplyCost number(13, 2) NULL,
    PS_Comment varchar2(200) NULL,
    skip varchar2(64) NULL
);

CREATE TABLE supplier(
    S_SuppKey int NULL,
    S_Name varchar2(64) NULL,
    S_Address varchar2(64) NULL,
    S_NationKey int NULL,
    S_Phone varchar2(18) NULL,
    S_AcctBal number(13, 2) NULL,
    S_Comment varchar2(105) NULL,
    skip varchar2(64) NULL
); 
     */
    private void inicializarPreparedStatements() throws SQLException {
        insertCustomer = con.prepareStatement("INSERT INTO Customer"
                + "(C_CustKey, C_Name, C_Address, C_NationKey, C_Phone, C_AcctBal, C_MktSegment, C_Comment, Skip)"
                + "VALUES (?,?,?,?,?,?,?,?,?)");
        insertlineitem = con.prepareStatement("INSERT INTO lineitem VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        insertNation = con.prepareStatement("INSERT INTO nation VALUES (?,?,?,?,?)");
        orders = con.prepareStatement("INSERT INTO orders VALUES (?,?,?,?,?,?,?,?,?,?)");
        part = con.prepareStatement("INSERT INTO part VALUES (?,?,?,?,?,?,?,?,?,?)");
        partsupp = con.prepareStatement("INSERT INTO partsupp VALUES (?,?,?,?,?,?)");
        /*        
        CREATE TABLE region(
            R_RegionKey int NULL,
            R_Name varchar2(64) NULL,
            R_Comment varchar2(160) NULL,
            skip varchar2(64) NULL
        );
        */
        region = con.prepareStatement("INSERT INTO region (R_RegionKey,R_Name, R_comment, skip) VALUES (?,?,?,?)");
        supplier = con.prepareStatement("INSERT INTO supplier VALUES (?,?,?,?,?,?,?,?)");
        
        select1 = con.prepareStatement("SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty,"
                + "sum(l_extendedprice) as sum_base_price, sum(l_extendedprice*(1-l_discount)) as "
                + "sum_disc_price, sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge, "
                + "avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) "
                + "as avg_disc, count(*) as count_order "
                + "FROM lineitem "
                + "WHERE l_shipdate <= ? "
                + "GROUP BY l_returnflag, l_linestatus "
                + "ORDER BY l_returnflag, l_linestatus");
        
        select2 = con.prepareStatement("SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment "
                + "FROM part, supplier, partsupp, nation, region "
                + "WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND p_size = ? "
                + "AND p_type like ? AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey "
                + "AND r_name = ? "
                + "AND ps_supplycost = (SELECT min(ps_supplycost) FROM partsupp, supplier, nation, region "
                    + "WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND s_nationkey = n_nationkey AND "
                    + "n_regionkey = r_regionkey AND r_name = ?) "
                + "ORDER BY s_acctbal desc, n_name, s_name, p_partkey");
        
        select3 = con.prepareStatement("SELECT l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue, o_orderdate, o_shippriority "
                + "FROM customer, orders, lineitem "
                + "WHERE c_mktsegment = ? AND c_custkey = o_custkey "
                + "AND l_orderkey = o_orderkey AND o_orderdate < ? AND l_shipdate > ? "
                + "GROUP BY l_orderkey, o_orderdate, o_shippriority "
                + "ORDER BY revenue desc, o_orderdate");
        
        select4 = con.prepareStatement("SELECT n_name, sum(l_extendedprice * (1 - l_discount)) as revenue "
                + "FROM customer, orders, lineitem, supplier, nation, region "
                + "WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey "
                + "AND l_suppkey = s_suppkey AND c_nationkey = s_nationkey AND s_nationkey = n_nationkey "
                + "AND n_regionkey = r_regionkey AND r_name = ? AND o_orderdate >= ? "
                + "AND o_orderdate < ? + interval '1' year "
                + "GROUP BY n_name "
                + "ORDER BY revenue desc");
    }

    public PreparedStatement getInsertCustomer() {
        return insertCustomer;
    }

    public PreparedStatement getInsertlineitem() {
        return insertlineitem;
    }

    public PreparedStatement getInsertNation() {
        return insertNation;
    }

    public PreparedStatement getOrders() {
        return orders;
    }

    public PreparedStatement getPart() {
        return part;
    }

    public PreparedStatement getPartsupp() {
        return partsupp;
    }

    public PreparedStatement getRegion() {
        return region;
    }

    public PreparedStatement getSupplier() {
        return supplier;
    }

    public PreparedStatement getSelect1() {
        return select1;
    }

    public PreparedStatement getSelect2() {
        return select2;
    }

    public PreparedStatement getSelect3() {
        return select3;
    }

    public PreparedStatement getSelect4() {
        return select4;
    }

}